using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

/**
*┌──────────────────────────────────────────────────────────────┐
*│　描    述：销售主表接口实现
*│　作    者：李宝
*│　版    本：1.0 模板代码自动生成
*│　创建时间：2019-01-05 16:55:24
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│　命名空间： Usido.CRM.Repository
*│　类    名： SalesOrderRepository
*└──────────────────────────────────────────────────────────────┘
*/

using Usido.CRM.Core.DbHelper;
using Usido.CRM.Core.Extensions;
using Usido.CRM.Core.Options;
using Usido.CRM.Core.Repository;
using Usido.CRM.Core.ToolHelper;
using Usido.CRM.Dto;
using Usido.CRM.IRepository;
using Usido.CRM.Models;

namespace Usido.CRM.Repository
{
    public class SalesOrderRepository : BaseRepository<SalesOrder, int>, ISalesOrderRepository
    {
        private readonly ILog logger = LogManager.GetLogger(nameof(SupplyOrderRepository));

        public SalesOrderRepository()
        {
            _dbConnection = ConnectionFactory.CreateConnection(DbOptionData.DbType, DbOptionData.ConnectionString);
        }

        public int AddSales(SalesOrder order, List<SalesInfo> orderInfoList)
        {
            IDbTransaction transaction;
            transaction = _dbConnection.BeginTransaction();

            string sqlAddSales = @"
INSERT INTO dbo.SalesOrder(OrderCode ,MemberId ,UserId ,CreateTime ,OrderType ,State ,Remark)
VALUES(@OrderCode, @MemberId, @UserId, GETDATE(), @OrderType, 0, @Remark)
SELECT @@IDENTITY";

            string sqlAddSalesInfo = @"
INSERT INTO dbo.SalesInfo( ProductId ,BatchNumber ,Quantity ,Price ,OrderId ,DateExpiry)
VALUES  ( @ProductId ,@BatchNumber ,@Quantity ,@Price ,@OrderId ,@DateExpiry)
";
            try
            {
                order.OrderCode = Tools.GetOrderCode(1);
                int orderId = _dbConnection.ExecuteScalar<int>(sqlAddSales, order, transaction);
                if (orderId <= 0)
                {
                    transaction.Rollback();
                    return -1;
                }

                foreach (var item in orderInfoList)
                {
                    item.OrderId = orderId;
                    _dbConnection.Execute(sqlAddSalesInfo, item, transaction);
                }

                transaction.Commit();

                return orderId;
            }
            catch (Exception ex)
            {
                logger.Error("添加销售信息时出错", ex);
                transaction.Rollback();

                return -1;
            }
        }

        public bool DeleteSales(int id)
        {
            string sql = @"DELETE dbo.SalesOrder WHERE Id = @OrderId;
DELETE dbo.SalesInfo WHERE OrderId = @OrderId";

            return _dbConnection.Execute(sql, new { OrderId = id }) > 0;
        }

        public string GetMoney(int id)
        {
            return _dbConnection.ExecuteScalar<string>("P_GetMoney", new { OrderId = id },
                commandType: CommandType.StoredProcedure);
        }

        public List<SalesInfoDto> GetSalesInfoList(int orderId)
        {
            string sql = @"
SELECT * FROM (
	SELECT A.*,
		   B.ProductName,
		   B.ProductCode,
		   B.Specification,
		   B.MarketingUnit,
		   B.ManufacturingEnterprise,
		   B.RetailPrice,
		   B.MemberDiscount AS MemberDiscount,
		   A.Price AS MemberPrice
	FROM dbo.SalesInfo AS A
		INNER JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
		INNER JOIN dbo.SalesOrder AS C ON A.OrderId = C.Id
	WHERE A.OrderId = @OrderId AND ISNULL(C.MemberId,0) > 0
	UNION ALL
	SELECT A.*,
		   B.ProductName,
		   B.ProductCode,
		   B.Specification,
		   B.MarketingUnit,
		   B.ManufacturingEnterprise,
		   A.Price AS RetailPrice,
		   B.MemberDiscount,
		   CONVERT(DECIMAL(18, 2), B.RetailPrice * ISNULL(B.MemberDiscount, 100) / 100) AS MemberPrice
	FROM dbo.SalesInfo AS A
		INNER JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
		INNER JOIN dbo.SalesOrder AS C ON A.OrderId = C.Id
	WHERE A.OrderId = @OrderId AND ISNULL(C.MemberId,0) <= 0
)T ";

            return _dbConnection.Query<SalesInfoDto>(sql, new { OrderId = orderId }).AsList();
        }

        public List<SalesInfoDto> GetSalesInfoReturnQuantityList(int orderId)
        {
            string sql = @"
SELECT * FROM (
	SELECT A.*,
		   B.ProductName,
		   B.ProductCode,
		   B.Specification,
		   B.MarketingUnit,
		   B.ManufacturingEnterprise,
		   B.RetailPrice,
		   B.MemberDiscount,
		   CONVERT(DECIMAL(18, 2), B.RetailPrice * ISNULL(B.MemberDiscount, 100) / 100) AS MemberPrice,
		   A.Quantity - dbo.P_GetSalesQuantityReturn(A.Id) AS QuantityReturned
	FROM dbo.SalesInfo AS A
		INNER JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
	WHERE A.OrderId = @OrderId
)T ";
            return _dbConnection.Query<SalesInfoDto>(sql, new { OrderId = orderId }).AsList();
        }

        public List<SalesOrderDto> GetSalesList(string where = "")
        {
            string sql = @"
SELECT * FROM (
	SELECT A.*,B.UserName,C.UserName AS MemberName FROM dbo.SalesOrder AS A
	INNER JOIN dbo.SysUser AS B ON A.UserId = B.Id
	LEFT JOIN dbo.MemberInfo AS C ON A.MemberId = C.Id
)T ";
            if (!where.IsNullOrEmpty())
            {
                sql += "WHERE  1=1 ";
                sql += where;
            }

            return _dbConnection.Query<SalesOrderDto>(sql).AsList();
        }

        public List<SalesReportDto> GetSalesReportList(string where = "")
        {
            //        string sql = @"
            //SELECT * FROM (
            //	SELECT A.*,B.OrderType,B.MemberId,B.CreateTime,B.UserId,B.OrderCode,C.ProductName,C.ProductCode,C.ManufacturingEnterprise,D.UserName, C.Specification , E.UserName AS MemberName,A.Quantity * A.Price AS TotalMoney
            //	FROM dbo.SalesInfo AS A
            //	INNER JOIN dbo.SalesOrder AS B ON A.OrderId = B.Id
            //	INNER JOIN dbo.SysProduct AS C ON A.ProductId = C.Id
            //	INNER JOIN dbo.SysUser AS D ON B.UserId = D.Id
            //	LEFT JOIN dbo.MemberInfo AS E ON B.MemberId = E.Id
            //    WHERE B.State = 1
            //)T ";
            string sql = @"
SELECT * FROM (
	SELECT A.Id,A.ProductId,A.BatchNumber,A.Quantity,A.Price,A.OrderId,A.DateExpiry,B.OrderType,B.MemberId,B.CreateTime,B.UserId,B.OrderCode,C.ProductName,
	C.ProductCode,C.ManufacturingEnterprise,D.UserName, C.Specification , E.UserName AS MemberName,A.Quantity * A.Price AS TotalMoney
	FROM dbo.SalesInfo AS A
	INNER JOIN dbo.SalesOrder AS B ON A.OrderId = B.Id
	INNER JOIN dbo.SysProduct AS C ON A.ProductId = C.Id
	INNER JOIN dbo.SysUser AS D ON B.UserId = D.Id
	LEFT JOIN dbo.MemberInfo AS E ON B.MemberId = E.Id
    WHERE B.State = 1
	UNION ALL
	SELECT  A.Id,A.ProductId,A.BatchNumber,A.Quantity,A.Price,A.OrderId,A.DateExpiry,E.OrderType,E.MemberId,B.CreateTime,B.UserId,B.OrderCode,C.ProductName,
	C.ProductCode, C.ManufacturingEnterprise, D.UserName, C.Specification, F.UserName AS MemberName, -A.Quantity * A.Price AS TotalMoney
	FROM dbo.SalesReturnInfo AS A
	INNER JOIN dbo.SalesReturnOrder AS B ON A.OrderId = B.Id
	INNER JOIN dbo.SysProduct AS C ON A.ProductId = C.Id
	INNER JOIN dbo.SysUser AS D ON B.UserId = D.Id
	INNER JOIN dbo.SalesOrder AS E ON B.OriginalSalesOrderId = E.Id
	LEFT JOIN dbo.MemberInfo AS F ON E.MemberId = F.Id
	WHERE B.State = 1
)T ";
            if (!where.IsNullOrEmpty())
            {
                sql += where;
            }

            return _dbConnection.Query<SalesReportDto>(sql).ToList();
        }

        public bool OutStore(int orderId, int userId)
        {
            var result = _dbConnection.ExecuteScalar<int>("P_SalesShipmentStorage", new { UserId = userId, OrderId = orderId }, commandType: CommandType.StoredProcedure);

            return result == 1;
        }

        public bool UpdateSales(SalesOrder order, List<SalesInfo> orderInfoList)
        {
            IDbTransaction transaction;
            transaction = _dbConnection.BeginTransaction();

            string sqlUpdateSales = @"UPDATE SalesOrder SET MemberId = @MemberId, Remark = @Remark WHERE Id = @Id";
            string sqlDeleteSalesInfo = @"DELETE dbo.SalesInfo WHERE OrderId = @OrderId";
            string sqlUpdateSalesInfo = @"
INSERT INTO dbo.SalesInfo( ProductId ,BatchNumber ,Quantity ,Price ,OrderId ,DateExpiry)
VALUES  ( @ProductId ,@BatchNumber ,@Quantity ,@Price ,@OrderId ,@DateExpiry)
";
            try
            {
                _dbConnection.Execute(sqlUpdateSales, order, transaction);
                _dbConnection.Execute(sqlDeleteSalesInfo, new { OrderId = order.Id }, transaction);

                foreach (var item in orderInfoList)
                {
                    _dbConnection.Execute(sqlUpdateSalesInfo, item, transaction);
                }

                transaction.Commit();

                return true;
            }
            catch (Exception ex)
            {
                logger.Error("修改销售信息时出错", ex);
                transaction.Rollback();

                return false;
            }
        }
    }
}